package com.aceleeyy.controller;

import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONUtil;
import com.aceleeyy.action.DataBaseQuery;
import com.aceleeyy.action.DataBaseQueryContext;
import com.aceleeyy.common.constant.AceleeyyCommonInfo;
import com.aceleeyy.common.constant.Result;
import com.aceleeyy.common.error.ErrorMessage;
import com.aceleeyy.common.web.controller.BaseController;
import com.aceleeyy.common.web.model.DBInfoVo;
import com.aceleeyy.common.web.model.DBSQLVo;
import com.aceleeyy.common.web.model.ExtendSource;
import com.google.common.base.Throwables;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 数据库连接
 *
 * @Author Ace Lee
 * @Date 2019/9/4 9:29
 * @Version 1.0
 **/
@Api(value = "数据库",description = "数据库")
@Slf4j
@RestController
@RequestMapping("/dbs")
public class DBController extends BaseController {

    @Autowired
    private DataBaseQueryContext dataBaseQueryContext;
    @Autowired
    private RedisTemplate redisTemplate;


    @ApiOperation(value = "执行SQL")
    @PostMapping("/execute/{id}")
    public Result execute(HttpServletRequest request,@PathVariable String id, @RequestBody DBSQLVo params) {
        log.info("执行SQL，请求参数：{},{}",id,params);
        Result result;

        //判断此连接信息是否存在
        String key = AceleeyyCommonInfo.REDIS_KEY_USER_DB+":"+id;
        Object o = request.getSession().getAttribute(key);
        if (null==o){
            result = Result.error(ErrorMessage.E_009.getErrDesc());
            return result;
        }

        //获取连接
        String value = (String) o;
        ExtendSource extendSource = JSONUtil.toBean(value, ExtendSource.class);
        DataBaseQuery dataBaseQuery = dataBaseQueryContext.build(extendSource.getDbType());
        if (null==dataBaseQuery){
            result = Result.error(ErrorMessage.E_004.getErrDesc());
            return result;
        }

        //判断SQL的增删改查
        String sql = params.getSql();
        if (StringUtils.isEmpty(sql)){
            result = Result.error(ErrorMessage.E_001.getErrDesc());
            return result;
        }
        String sqlPrefix = sql.trim().substring(0,6);
        //增删改
        if (sqlPrefix.equalsIgnoreCase(AceleeyyCommonInfo.SQL_TYPE_INSERT)
                || sqlPrefix.equalsIgnoreCase(AceleeyyCommonInfo.SQL_TYPE_DELETE)
                || sqlPrefix.equalsIgnoreCase(AceleeyyCommonInfo.SQL_TYPE_UPDATE) ){
            Optional<Integer> optionalDML = dataBaseQuery.executeDML(extendSource, sql);
            log.info("执行SQL，[增删改]结果：{}",optionalDML);
            Integer integer = null;
            if(optionalDML.isPresent()){
                integer = optionalDML.get();
            }
            result = new Result<>(integer);
        }else {
            Optional<List<Map<String, Object>>> optionalQ = dataBaseQuery.query(extendSource, sql);
            log.info("执行SQL，[查]结果：{}",optionalQ);
            List<Map<String, Object>> list = null;
            if (optionalQ.isPresent()){
                list = optionalQ.get();
            }
            result = new Result<>(list);
        }
        return result;
    }


    /**
     * 数据库连接
     *      如果连接成功，返回数据库元数据信息
     *      连接成功，将连接信息放入缓存，执行SQL的连接信息从缓存取出
     *      后台有定时清理长时间不适用的数据库连接
     *      退出连接时，清理缓存
     *
     * @param request
     * @param params 数据库连接信息
     * @return 数据库表和字段信息
     */
    @ApiOperation(value = "连接")
    @PostMapping("/conn")
    public Result conn(HttpServletRequest request, @RequestBody DBInfoVo params){
        log.info("数据库连接，请求参数：{}",params);
        Result result;
        Map<String,Object> map = Maps.newHashMap();
        List<Map<String,Object>> resDatas = Lists.newArrayList();

        try {
            //1.连接数据库信息
            String userName = params.getUsername();
            String password = params.getPassword();
            String jdbcUrl = params.getUrl();
            String dbType = params.getType();
            String schema = params.getSchema();
            if (StringUtils.isEmpty(jdbcUrl) || StringUtils.isEmpty(dbType)){
                return Result.error(ErrorMessage.E_001.getErrDesc());
            }

            ExtendSource extendSource = new ExtendSource();
            extendSource.setConnectionInfo("{\"userName\":\""+userName+"\",\"password\":\""+password+"\",\"jdbcUrl\":\""+jdbcUrl+"\"}");
            extendSource.setDbType(dbType);
            //2.给这次的连接一个唯一标识
            String id = IdUtil.randomUUID();
            map.put("id",id);
            extendSource.setId(id);

            //3.将extendSource对象存入session
            String key = AceleeyyCommonInfo.REDIS_KEY_USER_DB+":"+id;
            String value = JSONUtil.toJsonStr(extendSource);
            HttpSession session = request.getSession(AceleeyyCommonInfo.BOOLEAN_YES);
            session.setAttribute(key,value);
            session.setMaxInactiveInterval(AceleeyyCommonInfo.REDIS_KEY_USER_DB_TIME);//有效期(s)

            DataBaseQuery dataBaseQuery = dataBaseQueryContext.build(extendSource.getDbType());

            //4.根据数据库类型查询数据库元数据
            switch (dbType){
                case AceleeyyCommonInfo.DB_TYPE_MYSQL8:
                    getSchemaForMysql8(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                default:
                    return Result.error(ErrorMessage.E_004.getErrDesc());
            }
            map.put("schemas",resDatas);
            result=new Result<>(map);
        } catch (Exception e) {
            log.error("连接异常：{}", Throwables.getStackTraceAsString(e));
            throw new RuntimeException(ErrorMessage.E_999.getErrDesc());
        }
        return result;
    }


    private void getSchemaForMysql8(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "SELECT TABLE_SCHEMA as OWNER,TABLE_NAME,TABLE_TYPE,TABLE_COMMENT as COMMENTS  FROM information_schema. TABLES WHERE table_schema = '"+schema+"' ORDER BY TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[MySQL5]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "select COLUMN_NAME,DATA_TYPE,COLUMN_TYPE as DATA_LENGTH,COLUMN_COMMENT as COMMENTS from information_schema.columns  where table_schema = '"+schema+"'  and table_name = '"+
                            table.get("TABLE_NAME")+"'";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[MySQL5]表字段结果：{}",optionalC);
                setColumns(resDatas, optionalC, tables);
            }
        }
    }

    private void setColumns(List<Map<String, Object>> resDatas, Optional<Map<String, Object>> optionalC, List<Map<String, Object>> tables) {
        Map<String, Object> table;
        for (int i = 0; i < tables.size(); i++) {
            table = tables.get(i);
            if (optionalC.isPresent()) {
                Map<String, Object> tCloumns = optionalC.get();
                if (!tCloumns.isEmpty()) {
                    table.put("TABLE_COLUMNS", tCloumns.get(table.get("TABLE_NAME")));
                }
            }
            resDatas.add(table);
        }
    }

}
